package com.java.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import com.java.dao.CourierDao;
import com.java.model.Courier;
import com.java.model.PageBean;
import com.java.util.StringUtil;

/**
 * department
 * 
 * @author ld
 *
 */
@Repository("courierDao")
public class CourierDaoImpl implements CourierDao {

	@Resource
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<Courier> find(PageBean pageBean, Courier s_courier) {
		StringBuffer sb = new StringBuffer("select a.trueName, c.*, d.name companyName from t_user a left join t_user_courier b on a.id = b.userId left join t_courier c on b.courierId = c.id LEFT JOIN t_company d ON a.companyId = d.id and a.roleName='快递员' ");
		if (s_courier != null) {
			if (StringUtil.isNotEmpty(s_courier.getName())) {
				sb.append(" and a.trueName like '%" + s_courier.getName() + "%'");
			}
			if (StringUtil.isNotEmpty(s_courier.getAddress())) {
				sb.append(" and c.address like '%" + s_courier.getAddress() + "%'");
			}
		}
		if (pageBean != null) {
			sb.append(" limit " + pageBean.getStart() + "," + pageBean.getPageSize());
		}
		final List<Courier> courierList = new ArrayList<Courier>();
		System.out.println(sb.toString().replaceFirst("and", "where"));
		jdbcTemplate.query(sb.toString().replaceFirst("and", "where"), new Object[] {}, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Courier courier = new Courier();
				courier.setId(rs.getInt("id"));
				courier.setName(rs.getString("trueName"));
				courier.setAddress(rs.getString("address"));
				courier.setPhone(rs.getString("phone"));
				courier.setEmail(rs.getString("email"));
				courier.setCompanyName(rs.getString("companyName"));
				courierList.add(courier);
			}
		});
		return courierList;
	}

	@Override
	public int count(Courier s_courier) {
//		"select count(*) from t_courier"
		StringBuffer sb = new StringBuffer("select COUNT(a.trueName) from t_user a left join t_user_courier b on a.id = b.userId left join t_courier c on b.courierId = c.id  LEFT JOIN t_company d ON a.companyId = d.id and a.roleName='快递员' ");
		if (s_courier != null) {
			if (StringUtil.isNotEmpty(s_courier.getName())) {
				sb.append(" and a.trueName like '%" + s_courier.getName() + "%'");
			}
			if (StringUtil.isNotEmpty(s_courier.getAddress())) {
				sb.append(" and c.address like '%" + s_courier.getAddress() + "%'");
			}
		}
		return jdbcTemplate.queryForObject(sb.toString().replaceFirst("and", "where"), Integer.class);
	}

	@Override
	public void add(Courier courier) {
		String sql = "insert into t_courier values(null,?,?,?)";
		jdbcTemplate.update(sql,
				new Object[] { courier.getPhone(), courier.getEmail(), courier.getAddress() });
	}

	@Override
	public Courier loadById(int id) {
		String sql = "select * from t_courier where id=?";
		final Courier courier = new Courier();
		jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				courier.setId(rs.getInt("id"));
				courier.setName(rs.getString("name"));
				courier.setAddress(rs.getString("address"));
				courier.setPhone(rs.getString("phone"));
				courier.setEmail(rs.getString("email"));
			}
		});
		return courier;
	}

	@Override
	public void update(Courier courier) {
		String sql = "update t_courier set name=?,address=?,phone=?,email=? where id=?";
		jdbcTemplate.update(sql, new Object[] { courier.getName(), courier.getAddress(), courier.getPhone(),
				courier.getEmail(), courier.getId() });
	}

	@Override
	public void delete(int id) {
		String sql = "delete from t_courier where id=?";
		jdbcTemplate.update(sql, new Object[] { id });
	}

}
